﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using TimeTableManagement.Entities;
using TimeTableManagement;
using System.Data.OleDb;
using System.Web.Script.Serialization;
using TimeTableManagement.Models;

namespace TimeTableManagement.Controllers
{ 
    [Authorize]
    public class StudentController : Controller
    {
        private QLSVContext db = new QLSVContext();

        
        //
        // GET: /Student/


        public ViewResult Index()
        {
            var tb_people = db.Tb_People.Include(t => t.Tb_Department).Where(t=>t.type==13);
            return View(tb_people.ToList());
        }

        //
        // GET: /Student/Details/5

        public ViewResult Details(int id)
        {
            Tb_People tb_people = db.Tb_People.Find(id);
            return View(tb_people);
        }

        //
        // GET: /Student/Create

        public ActionResult Create()
        {
            ViewBag.id_department = new SelectList(db.Tb_Department, "id", "name");
            return View();
        } 

        //
        // POST: /Student/Create

        [HttpPost]
        public ActionResult Create(Tb_People tb_people, string profile)
        {
            if (ModelState.IsValid)
            {
                if (checknotexits(tb_people))
                {
                    db.Tb_People.Add(tb_people);
                    db.SaveChanges();
                    Tb_People peoplenew = new Tb_People();
                    var _listtem = db.Tb_People.Where(g => g.mssv.Equals(tb_people.mssv)).ToList();
                    foreach (Tb_People p in _listtem)
                    {
                        peoplenew = p;
                        break;
                    }
                    JavaScriptSerializer jss = new JavaScriptSerializer();
                    List<Profile> listprofile = jss.Deserialize<List<Profile>>(profile);
                    String email = "";
                    foreach (Profile _profile in listprofile)
                    {
                        Tb_Profile_Value pro_value = new Tb_Profile_Value();
                        pro_value.id_person = peoplenew.id;
                        pro_value.code = peoplenew.mssv;
                        pro_value.id_keyworkprofile = _profile.id;
                        pro_value.value = _profile.value;
                        db.Tb_Profile_Value.Add(pro_value);
                        db.SaveChanges();
                        if (_profile.key.Equals("email")) email = _profile.value;
                    }

                    //Them mot user trong Tb_Userlogon voi username, password = mssv
                    Tb_Userlogin userlogin = new Tb_Userlogin();
                    userlogin.username = tb_people.mssv;
                    userlogin.password = Functions.Encrypt(tb_people.mssv);
                    userlogin.email = email;
                    userlogin.displayname = tb_people.name;
                    userlogin.id_role = 9;
                    userlogin.rememberme = false;
                    db.Tb_Userlogin.Add(userlogin);
                    db.SaveChanges();
                    return RedirectToAction("Index");
                }
                else
                {
                    ModelState.AddModelError("", "Bi trung MSSV.");
                }
            }

            ViewBag.id_department = new SelectList(db.Tb_Department, "id", "code", tb_people.id_department);
            return View(tb_people);
        }
        private bool checknotexits(Tb_People people)
        {
            var p = db.Tb_People.Where(g => g.mssv.Equals(people.mssv)).ToList();
            if (p.Count == 0) return true; //khong ton tai
            return false;//da ton tai
        }
        //
        // GET: /Student/Edit/5
 
        public ActionResult Edit(int id)
        {
            Tb_People tb_people = db.Tb_People.Find(id);
            ViewBag.id_department = new SelectList(db.Tb_Department, "id", "name", tb_people.id_department);
            return View(tb_people);
        }

        //
        // POST: /Student/Edit/5

        [HttpPost]
        public ActionResult Edit(Tb_People tb_people, string profile)
        {
            if (ModelState.IsValid)
            {
                JavaScriptSerializer jss = new JavaScriptSerializer();
                List<Profile> listprofile = jss.Deserialize<List<Profile>>(profile);
                foreach(Profile _profile in listprofile)
                {
                    var PR = db.Tb_Profile_Value.Find(_profile.id);
                    PR.value = _profile.value;
                    db.SaveChanges();
                }
                db.Entry(tb_people).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            ViewBag.id_department = new SelectList(db.Tb_Department, "id", "code", tb_people.id_department);
            return View(tb_people);
        }

        //
        // GET: /Student/Delete/5
 
        public ActionResult Delete(int id)
        {
            Tb_People tb_people = db.Tb_People.Find(id);
            return View(tb_people);
        }

        //
        // POST: /Student/Delete/5

        [HttpPost, ActionName("Delete")]
        public ActionResult DeleteConfirmed(int id)
        {            
            Tb_People tb_people = db.Tb_People.Find(id);
            db.Tb_People.Remove(tb_people);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        public ActionResult Import(string filename)
        {
            if (filename == null)
            {
                ViewData["kq"] = "no";
                return View();
            }
            else
            {
                if (this.importpeople(filename)) ViewData["kq"] = "ok";
                else ViewData["kq"] = "un";
                return RedirectToAction("Index");
            }
        }

        public ActionResult Registry()
        {
            var hkpre = db.Tb_Config.Where(g => g.keyword.Equals("termrepair"));
            int HK = 0;
            String hk="";
            foreach (Tb_Config _c in hkpre)
            {
                HK = Int32.Parse(_c.value);
                hk= _c.displayword;
                if(HK!= 0) break;
            }
            ViewBag.HK = HK;//hoc ky chuan bi dang ky
            int id_person = 0;
            try
            {
                id_person = (int)Session["IDPEOPLE"];
            }
            catch
            {
                return RedirectToAction("LogOn", "Account", new { ReturnUrl = "/Student/Registry" });
            }
            String ttt = HK.ToString();
            var _people = db.Tb_Profile_Value
                .Where(g => g.id_person == id_person)
                .Where(h => h.Tb_Config.keyword.Equals("regcou"))
                .Where(j=>j.option.Equals(ttt));
            if (_people.ToList().Count == 0)
            {
                //ModelState.AddModelError("", "Ban chua dang ky mon hoc, vui long dang ky");
                ViewBag.RED = 1;
                return View(new List<RegistryCourse>());
            }
            String ass = "";
            foreach (Tb_Profile_Value pv in _people)
            {
                ass = pv.value;
                break;
            }
            JavaScriptSerializer jss = new JavaScriptSerializer();
            List<RegistryCourse> listprofile = jss.Deserialize<List<RegistryCourse>>(ass);
            return View(listprofile);
        }
        [HttpPost]
        public ActionResult Registry(String registry, int sumcredit)
        {
            Tb_Config _mc = db.Tb_Config.Find(24);
            var hkpre = db.Tb_Config.Where(g => g.keyword.Equals("termrepair"));
            int HK = 0;
            foreach (Tb_Config _c in hkpre)
            {
                HK = Int32.Parse(_c.value);
                if (HK != 0) break;
            }
            ViewBag.HK = HK;
            int maxcredit = Int32.Parse(_mc.value);
            if (sumcredit > maxcredit)
            {
                ModelState.AddModelError("", "Ban da dang ky vuot qua so tin chi quy dinh. Tong so tin chi dang ky phai nho hon " + maxcredit);
                ViewBag.RED = 1;
                return View();
            }
            else
            {
                //luu vao tb_profile_value
                int id_person = 0;
                try
                {
                    id_person = (int)Session["IDPEOPLE"];
                }
                catch
                {
                    return RedirectToAction("LogOn", "Account", new { ReturnUrl = "/Student/Registry" });
                }
                Tb_People peo = db.Tb_People.Find(id_person);
                String codeperson = peo.mssv;
                int id_keyword = 34;
                String ttt = HK.ToString();
                //tim xem da co trong tb_profile_value chua
                var y = db.Tb_Profile_Value
                    .Where(g => g.id_person == id_person)
                    .Where(h => h.id_keyworkprofile == id_keyword)
                    .Where(j=>j.option.Equals(ttt));
                if (y.ToList().Count != 0)
                {
                    // da ton tai -> cap nhat gia tri
                    Nullable<int> id_profile_value = null;
                    foreach (Tb_Profile_Value ii in y)
                    {
                        id_profile_value = ii.id;
                        break;
                    }
                    var pro = db.Tb_Profile_Value.Find(id_profile_value);
                    pro.value = registry;
                    db.SaveChanges();
                }
                else
                {
                    //chua ton tai -> them moi
                    Tb_Profile_Value pro = new Tb_Profile_Value();
                    pro.id_person = id_person;
                    pro.id_keyworkprofile = id_keyword;
                    pro.code = codeperson;
                    pro.value = registry;
                    pro.option = ttt;
                    db.Tb_Profile_Value.Add(pro);
                    db.SaveChanges();
                    plusone(registry);
                }
            }

            JavaScriptSerializer jss = new JavaScriptSerializer();
            List<RegistryCourse> listprofile = jss.Deserialize<List<RegistryCourse>>(registry);
            return View(listprofile);
        }
        private void plusone(string registry)
        {
            JavaScriptSerializer jss = new JavaScriptSerializer();
            List<RegistryCourse> listprofile = jss.Deserialize<List<RegistryCourse>>(registry);
            foreach (RegistryCourse reg in listprofile)

            {
                var T = db.Tb_Timetable.Find(reg.id);
                T.count++;
                db.SaveChanges();
            }
        }
        public ActionResult Timetable()
        {
            List<TimeTable> listtimetable = new List<TimeTable>();
            int idstudent = 0;
            try
            {
                idstudent = (int)Session["IDPEOPLE"];
            }
            catch
            {
                return RedirectToAction("LogOn", "Account", new { ReturnUrl = "/Student/Registry" });
            }
            //kiem tra xem day co phai la sv hay khong
            int? type = db.Tb_People.Find(idstudent).type;
            if (type != 13)
            {
                ViewBag.error = 1;
                return View(new List<TimeTable>());
            }
            //lay hk hien tai
            var hkactive = db.Tb_Config.Where(g => g.keyword.Equals("termactive"));
            int HK = 0;
            foreach (Tb_Config _c in hkactive)
            {
                HK = Int32.Parse(_c.value);
                if (HK != 0) break;
            }
            if (HK == 0)
            {

                ModelState.AddModelError("", "Error System Timetable");
                return View(new List<TimeTable>());
            }
            //Lay danh sach cac mon hoc ma sinh vien nay dang ky
            var list = db.Tb_CourseRegister
                .Where(g => g.id_student == idstudent)
                .Where(g => g.Tb_Timetable.id_term == HK);
            foreach (Tb_CourseRegister c in list)
            {
                TimeTable time = new TimeTable();
                time.id_course = c.Tb_Timetable.id_course;
                time.id_lecturer = c.Tb_Timetable.id_lecturer;
                time.id_room = c.Tb_Timetable.id_room;
                time.id_slot = c.Tb_Timetable.id_slot;
                time.yesorno = true;
                time.code_course = c.Tb_Timetable.Tb_Course.code;
                time.name_course = c.Tb_Timetable.Tb_Course.name;
                time.name_lecturer = c.Tb_Timetable.Tb_People.name;
                time.name_room = c.Tb_Timetable.Tb_Room.name;
                time.name_slot = c.Tb_Timetable.Tb_Slot.slot;
                listtimetable.Add(time);
            }
            return View(listtimetable);
        }

        private bool importpeople(String fi)
        {
            //string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/Uploads/" + fi) + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";";
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/Uploads/" + fi) + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
            // Create the connection object
            OleDbConnection oledbConn = new OleDbConnection(connString);
            try
            {
                // Open connection
                oledbConn.Open();

                // Create OleDbCommand object and select data from worksheet Sheet1
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);

                // Create new OleDbDataAdapter
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                DataSet ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.Fill(ds);
                int i = 0;
                DataRow r = null;
                while ((r = ds.Tables[0].Rows[i]) != null)
                {
                    Array t = r.ItemArray;
                    Tb_People people = new Tb_People();
                    people.mssv = (string)t.GetValue(0);
                    people.name = (string)t.GetValue(1);
                    people.type = 13;
                    string codedepar = (string)t.GetValue(2);
                    var _d = db.Tb_Department.Where(g => g.code.Equals(codedepar));
                    people.id_department = _d.First<Tb_Department>().id;
                    //Them Tb_People
                    bool checknotexit = true;
                    var p = db.Tb_People.Where(g => g.mssv.Equals(people.mssv)).ToList();
                    if (p.Count != 0) checknotexit = false; //khong ton tai
                    if (checknotexit)
                    {
                        db.Tb_People.Add(people);
                        String email = (string)t.GetValue(3);
                        Tb_Userlogin userlogin = new Tb_Userlogin();
                        userlogin.username = people.mssv;
                        userlogin.password = Functions.Encrypt(people.mssv);
                        userlogin.displayname = people.name;
                        userlogin.email = email;
                        userlogin.id_role = 9;
                        userlogin.rememberme = false;
                        //Them Tb_Userlogin
                        db.Tb_Userlogin.Add(userlogin);
                        db.SaveChanges();
                        
                        //Them profile ""
                        var itemperson = db.Tb_People.Where(g => g.mssv.Equals(people.mssv));
                        int id_person = itemperson.First<Tb_People>().id;
                        var itemprofile = db.Tb_Config
                            .Where(g => g.option.Equals("profile"));
                        foreach (Tb_Config con in itemprofile)
                        {
                            Tb_Profile_Value profile_value = new Tb_Profile_Value();
                            profile_value.code = people.mssv;
                            profile_value.value = "";
                            profile_value.id_keyworkprofile = con.id;
                            profile_value.id_person = id_person;
                            QLSVContext _c = new QLSVContext();
                            _c.Tb_Profile_Value.Add(profile_value);
                            _c.SaveChanges();
                            _c.Dispose();
                        }
                        
                    }
                    i++;
                }
            }
            catch (Exception ex)
            {
                Response.Write(ex);
                return false;
            }
            finally
            {
                oledbConn.Close();
            }
            return true;
        }
        protected override void Dispose(bool disposing)
        {
            db.Dispose();
            base.Dispose(disposing);
        }
    }
    
    public class RegistryCourse
    {
        int _id; //id_timetable
        int _idc; //id_course
        String _codec; //Tb_Course.code
        public String codec
        {
            get { return _codec; }
            set { _codec = value; }
        }
        public int id
        {
            get { return _id; }
            set { _id = value; }
        }
        public int idc
        {
            get { return _idc; }
            set { _idc = value; }
        }
    }
}